﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace ThuatToanDAO
{
    public class ItemDAO
    {
        public ItemDAO()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        /// <summary>
        /// Get string connection
        /// </summary>
        /// <returns>string connection</returns>
        public string GetConnectString()
        {
            // Get connection string from config file
            // string strConnectString = "Data Source=HCD-FRESHER069;Initial Catalog=MockProject_Iteration3;User ID=sa; Password=123456789;";
            string strConnectString = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;//Get connection string
            return strConnectString;
        }


        #region Select...

        // <summary>
        /// Get table to dataset
        /// </summary>
        /// <param name="procedureName">procedure name</param>
        /// <param name="parameters">parameter[]</param>
        /// <returns>DataSet</returns>
        public DataSet ExecuteSelectSingle(String parameters)
        {
            DataSet dsResult = new DataSet(); // Initialize an objects DataTable
            string strConnectString = GetConnectString();
            using (SqlConnection sqlConnection = new SqlConnection(strConnectString))
            {

                String selectString = "SELECT * FROM Item WHERE ItemID = @ItemID";

                sqlConnection.Open(); // Open connection

                SqlCommand sqlCommand = new SqlCommand(); // Initialize an object SqlCommand
                sqlCommand.CommandType = CommandType.Text; // Specifies how a command string is interpreted.
                sqlCommand.CommandText = selectString; // Name of procudere want to execute
                sqlCommand.Connection = sqlConnection; // Set connection
                // Set parameter
                if (parameters != null) // If parameter is not null
                {
                    //foreach (SqlParameter param in parameters) // Foreach parameters[]
                    {
                        sqlCommand.Parameters.Add(new SqlParameter("ItemID", parameters)); // Add param to com.Parameters
                    }
                }

                SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
                da.Fill(dsResult); // Fill data to dsResult

                sqlCommand.Dispose(); // Dispose object SqlCommand com
                sqlConnection.Close(); // Close connection
            }
            return dsResult; // Return a DataSet
        }

        // <summary>
        /// Get table to dataset
        /// </summary>
        /// <param name="procedureName">procedure name</param>
        /// <param name="parameters">parameter[]</param>
        /// <returns>DataSet</returns>
        public DataSet ExecuteSelectTopView()
        {
            DataSet dsResult = new DataSet(); // Initialize an objects DataTable
            string strConnectString = GetConnectString();
            using (SqlConnection sqlConnection = new SqlConnection(strConnectString))
            {

                String selectString = "SELECT  TOP (50)  ItemID, Name FROM Item ORDER BY ViewCount DESC";

                sqlConnection.Open(); // Open connection

                SqlCommand sqlCommand = new SqlCommand(); // Initialize an object SqlCommand
                sqlCommand.CommandType = CommandType.Text; // Specifies how a command string is interpreted.
                sqlCommand.CommandText = selectString; // Name of procudere want to execute
                sqlCommand.Connection = sqlConnection; // Set connection

                SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
                da.Fill(dsResult); // Fill data to dsResult

                sqlCommand.Dispose(); // Dispose object SqlCommand com
                sqlConnection.Close(); // Close connection
            }
            return dsResult; // Return a DataSet
        }

        // <summary>
        /// Get table to dataset
        /// </summary>
        /// <param name="procedureName">procedure name</param>
        /// <param name="parameters">parameter[]</param>
        /// <returns>DataSet</returns>
        public DataSet ExecuteSelectToTalItem()
        {
            DataSet dsResult = new DataSet(); // Initialize an objects DataTable
            string strConnectString = GetConnectString();
            using (SqlConnection sqlConnection = new SqlConnection(strConnectString))
            {

                String selectString = "SELECT  Count(*) as Total FROM Item";

                sqlConnection.Open(); // Open connection

                SqlCommand sqlCommand = new SqlCommand(); // Initialize an object SqlCommand
                sqlCommand.CommandType = CommandType.Text; // Specifies how a command string is interpreted.
                sqlCommand.CommandText = selectString; // Name of procudere want to execute
                sqlCommand.Connection = sqlConnection; // Set connection

                SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
                da.Fill(dsResult); // Fill data to dsResult

                sqlCommand.Dispose(); // Dispose object SqlCommand com
                sqlConnection.Close(); // Close connection
            }
            return dsResult; // Return a DataSet
        }


        // <summary>
        /// Get table to dataset
        /// </summary>
        /// <param name="procedureName">procedure name</param>
        /// <param name="parameters">parameter[]</param>
        /// <returns>DataSet</returns>
        public DataSet ExecuteSelectALL()
        {
            DataSet dsResult = new DataSet(); // Initialize an objects DataTable
            string strConnectString = GetConnectString();
            using (SqlConnection sqlConnection = new SqlConnection(strConnectString))
            {

                String selectString = "SELECT * FROM Item";

                sqlConnection.Open(); // Open connection

                SqlCommand sqlCommand = new SqlCommand(); // Initialize an object SqlCommand
                sqlCommand.CommandType = CommandType.Text; // Specifies how a command string is interpreted.
                sqlCommand.CommandText = selectString; // Name of procudere want to execute
                sqlCommand.Connection = sqlConnection; // Set connection

                SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
                da.Fill(dsResult); // Fill data to dsResult

                sqlCommand.Dispose(); // Dispose object SqlCommand com
                sqlConnection.Close(); // Close connection
            }
            return dsResult; // Return a DataSet
        }


        public DataSet ExecuteSelectFollowItemID(int ItemID)
        {
            DataSet dsResult = new DataSet(); // Initialize an objects DataTable
            string strConnectString = GetConnectString();
            using (SqlConnection sqlConnection = new SqlConnection(strConnectString))
            {

                String selectString = "SELECT * FROM Item WHERE ItemID = @ItemID";

                sqlConnection.Open(); // Open connection

                SqlCommand sqlCommand = new SqlCommand(); // Initialize an object SqlCommand
                sqlCommand.CommandType = CommandType.Text; // Specifies how a command string is interpreted.
                sqlCommand.CommandText = selectString; // Name of procudere want to execute
                sqlCommand.Connection = sqlConnection; // Set connection

                // Set parameter
                if (ItemID > 0) // If parameter is not null
                {
                    //foreach (SqlParameter param in parameters) // Foreach parameters[]
                    {
                        sqlCommand.Parameters.Add(new SqlParameter("ItemID", ItemID)); // Add param to com.Parameters
                    }
                }
                SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
                da.Fill(dsResult); // Fill data to dsResult

                sqlCommand.Dispose(); // Dispose object SqlCommand com
                sqlConnection.Close(); // Close connection
            }
            return dsResult; // Return a DataSet
        }


        public DataSet ExecuteSelectFollowCategoryID(string CategoryID)
        {
            DataSet dsResult = new DataSet(); // Initialize an objects DataTable
            string strConnectString = GetConnectString();
            using (SqlConnection sqlConnection = new SqlConnection(strConnectString))
            {

                String selectString = "SELECT * FROM Item WHERE CategoryID LIKE @CategoryID + '%' ";

                sqlConnection.Open(); // Open connection

                SqlCommand sqlCommand = new SqlCommand(); // Initialize an object SqlCommand
                sqlCommand.CommandType = CommandType.Text; // Specifies how a command string is interpreted.
                sqlCommand.CommandText = selectString; // Name of procudere want to execute
                sqlCommand.Connection = sqlConnection; // Set connection

                // Set parameter
                
                sqlCommand.Parameters.Add(new SqlParameter("CategoryID", CategoryID)); // Add param to com.Parameters
                
                SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
                da.Fill(dsResult); // Fill data to dsResult

                sqlCommand.Dispose(); // Dispose object SqlCommand com
                sqlConnection.Close(); // Close connection
            }
            return dsResult; // Return a DataSet
        }


        public DataSet ExecuteSelectItemRelated(int ItemID)
        {
            DataSet dsResult = new DataSet(); // Initialize an objects DataTable
            string strConnectString = GetConnectString();
            using (SqlConnection sqlConnection = new SqlConnection(strConnectString))
            {

                String selectString = "SELECT * FROM Item WHERE "
                                        + "(CategoryID IN (SELECT CategoryID FROM Item WHERE ItemID = @ItemID)) "
                                        + "AND (ItemID <> @ItemID)";

                sqlConnection.Open(); // Open connection

                SqlCommand sqlCommand = new SqlCommand(); // Initialize an object SqlCommand
                sqlCommand.CommandType = CommandType.Text; // Specifies how a command string is interpreted.
                sqlCommand.CommandText = selectString; // Name of procudere want to execute
                sqlCommand.Connection = sqlConnection; // Set connection

                // Set parameter
                if (ItemID > 0) // If parameter is not null
                {
                    //foreach (SqlParameter param in parameters) // Foreach parameters[]
                    {
                        sqlCommand.Parameters.Add(new SqlParameter("ItemID", ItemID)); // Add param to com.Parameters
                    }
                }
                SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
                da.Fill(dsResult); // Fill data to dsResult

                sqlCommand.Dispose(); // Dispose object SqlCommand com
                sqlConnection.Close(); // Close connection
            }
            return dsResult; // Return a DataSet
        }

        public DataSet ExecuteSearch(List<SqlParameter> parameters)
        {
            DataSet dsResult = new DataSet(); // Initialize an objects DataTable
            string strConnectString = GetConnectString();
            using (SqlConnection sqlConnection = new SqlConnection(strConnectString))
            {

                String selectString =  "SELECT Item.ItemID, Item.Name, Category.CategoryID, Category.Name AS CategoryName"
                                       + " FROM Item INNER JOIN Category ON Item.CategoryID = Category.CategoryID"
                                       //+ " WHERE (Item.Name LIKE '%' + @Name + '%')"
                                       + " WHERE FREETEXT(Item.Name, N'@Name')"
                                       + " AND ((@CategoryID = - 1) OR (Item.CategoryID = @CategoryID))";

                sqlConnection.Open(); // Open connection

                SqlCommand sqlCommand = new SqlCommand(); // Initialize an object SqlCommand
                sqlCommand.CommandType = CommandType.Text; // Specifies how a command string is interpreted.
                sqlCommand.CommandText = selectString; // Name of procudere want to execute
                sqlCommand.Connection = sqlConnection; // Set connection

                // Set parameter
                if (parameters != null) // If parameter is not null
                {
                    foreach (SqlParameter param in parameters) // Foreach parameters[]
                    {
                        sqlCommand.Parameters.Add(param); // Add param to Parameters
                    }
                }

                SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
                da.Fill(dsResult); // Fill data to dsResult

                sqlCommand.Dispose(); // Dispose object SqlCommand com
                sqlConnection.Close(); // Close connection
            }
            return dsResult; // Return a DataSet
        }


        public DataSet ExecuteSearchByCategory(List<SqlParameter> parameters)
        {
            DataSet dsResult = new DataSet(); // Initialize an objects DataTable
            string strConnectString = GetConnectString();
            using (SqlConnection sqlConnection = new SqlConnection(strConnectString))
            {

                String selectString =  "SELECT     Item.ItemID, Item.Name, Category.CategoryID, Category.Name AS CategoryName"
                                        + " FROM Category INNER JOIN Item ON Category.CategoryID = Item.CategoryID"
                                        + " WHERE (Category.CategoryID LIKE @CategoryID + '%')"
                                        + " ORDER BY Category.CategoryID";
                
                sqlConnection.Open(); // Open connection

                SqlCommand sqlCommand = new SqlCommand(); // Initialize an object SqlCommand
                sqlCommand.CommandType = CommandType.Text; // Specifies how a command string is interpreted.
                sqlCommand.CommandText = selectString; // Name of procudere want to execute
                sqlCommand.Connection = sqlConnection; // Set connection

                // Set parameter
                if (parameters != null) // If parameter is not null
                {
                    foreach (SqlParameter param in parameters) // Foreach parameters[]
                    {
                        sqlCommand.Parameters.Add(param); // Add param to Parameters
                    }
                }

                SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
                da.Fill(dsResult); // Fill data to dsResult

                sqlCommand.Dispose(); // Dispose object SqlCommand com
                sqlConnection.Close(); // Close connection
            }
            return dsResult; // Return a DataSet
        }


        //Search paging
        public DataSet ExecuteSelectTotalItem(List<SqlParameter> parameters)
        {
            DataSet dsResult = new DataSet(); // Initialize an objects DataTable
            string strConnectString = GetConnectString();
            using (SqlConnection sqlConnection = new SqlConnection(strConnectString))
            {

                String selectString = "SELECT COUNT(ItemID) AS Count FROM Item  WHERE Name LIKE '%' + @Name + '%')";

                sqlConnection.Open(); // Open connection

                SqlCommand sqlCommand = new SqlCommand(); // Initialize an object SqlCommand
                sqlCommand.CommandType = CommandType.Text; // Specifies how a command string is interpreted.
                sqlCommand.CommandText = selectString; // Name of procudere want to execute
                sqlCommand.Connection = sqlConnection; // Set connection

                // Set parameter
                if (parameters != null) // If parameter is not null
                {
                    foreach (SqlParameter param in parameters) // Foreach parameters[]
                    {
                        sqlCommand.Parameters.Add(param); // Add param to Parameters
                    }
                }

                SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
                da.Fill(dsResult); // Fill data to dsResult

                sqlCommand.Dispose(); // Dispose object SqlCommand com
                sqlConnection.Close(); // Close connection
            }
            return dsResult; // Return a DataSet
        }


        public DataSet ExecuteSearchPagingByItemName(List<SqlParameter> parameters)
        {
            DataSet dsResult = new DataSet(); // Initialize an objects DataTable
            string strConnectString = GetConnectString();
            using (SqlConnection sqlConnection = new SqlConnection(strConnectString))
            {

                String selectString = "SELECT TOP 30  * FROM" +
                                       "(" +
                                          "SELECT Item.ItemID, Item.Name, Category.CategoryID, Category.Name AS CategoryName, ROW_NUMBER() OVER (ORDER BY Item.ItemID) AS num" +
                                           "FROM Item INNER JOIN Category ON Item.CategoryID = Category.CategoryID" +
                                           " WHERE (Item.Name LIKE '%' + @Name + '%')" +
                                       ") AS a" +
                                       "WHERE num > @Start";

                sqlConnection.Open(); // Open connection

                SqlCommand sqlCommand = new SqlCommand(); // Initialize an object SqlCommand
                sqlCommand.CommandType = CommandType.Text; // Specifies how a command string is interpreted.
                sqlCommand.CommandText = selectString; // Name of procudere want to execute
                sqlCommand.Connection = sqlConnection; // Set connection

                // Set parameter
                if (parameters != null) // If parameter is not null
                {
                    foreach (SqlParameter param in parameters) // Foreach parameters[]
                    {
                        sqlCommand.Parameters.Add(param); // Add param to Parameters
                    }
                }

                SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
                da.Fill(dsResult); // Fill data to dsResult

                sqlCommand.Dispose(); // Dispose object SqlCommand com
                sqlConnection.Close(); // Close connection
            }
            return dsResult; // Return a DataSet
        }

        #endregion



        #region Insert...

        // <summary>
        /// Get table to Item
        /// </summary>
        /// <param name="procedureName">procedure name</param>
        /// <param name="parameters">parameter[]</param>
        /// <returns>void</returns>
        public void ExecuteInsert(List<SqlParameter> parameters)
        {
            string strConnectString = GetConnectString();
            using (SqlConnection sqlConnection = new SqlConnection(strConnectString))
            {

                String insertString = "INSERT INTO Item VALUES (@CategoryID, @Name, @Description, @Demo, @Other1, @Other2, @CreateDate, @ViewCount)";

                sqlConnection.Open(); // Open connection

                SqlCommand sqlCommand = new SqlCommand(); // Initialize an object SqlCommand
                sqlCommand.CommandType = CommandType.Text; // Specifies how a command string is interpreted.
                sqlCommand.CommandText = insertString; // Name of procudere want to execute
                sqlCommand.Connection = sqlConnection; // Set connection
                // Set parameter
                if (parameters != null) // If parameter is not null
                {
                    foreach (SqlParameter param in parameters) // Foreach parameters[]
                    {
                        sqlCommand.Parameters.Add(param); // Add param to Parameters
                    }
                }

                sqlCommand.ExecuteNonQuery();

                sqlCommand.Dispose(); // Dispose object SqlCommand com
                sqlConnection.Close(); // Close connection
            }

        }


        #endregion


        #region Update...
        // <summary>
        /// Get table to Item
        /// </summary>
        /// <param name="procedureName">procedure name</param>
        /// <param name="parameters">parameter[]</param>
        /// <returns>void</returns>
        public void ExecuteUpdate(List<SqlParameter> parameters)
        {
            string strConnectString = GetConnectString();
            using (SqlConnection sqlConnection = new SqlConnection(strConnectString))
            {

                String insertString = "UPDATE Item SET CategoryID = @CategoryID, Name = @Name, Description = @Description, Demo = @Demo, Other1 = @Other1, Other2 = @Other2 WHERE ItemID = @ItemID";
                
                sqlConnection.Open(); // Open connection

                SqlCommand sqlCommand = new SqlCommand(); // Initialize an object SqlCommand
                sqlCommand.CommandType = CommandType.Text; // Specifies how a command string is interpreted.
                sqlCommand.CommandText = insertString; // Name of procudere want to execute
                sqlCommand.Connection = sqlConnection; // Set connection
                // Set parameter
                if (parameters != null) // If parameter is not null
                {
                    foreach (SqlParameter param in parameters) // Foreach parameters[]
                    {
                        sqlCommand.Parameters.Add(param); // Add param to Parameters
                    }
                }

                sqlCommand.ExecuteNonQuery();

                sqlCommand.Dispose(); // Dispose object SqlCommand com
                sqlConnection.Close(); // Close connection
            }

        }


        // <summary>
        /// Get table to Item
        /// </summary>
        /// <param name="procedureName">procedure name</param>
        /// <param name="parameters">parameter[]</param>
        /// <returns>void</returns>
        public void ExecuteUpdateViewCount(List<SqlParameter> parameters)
        {
            string strConnectString = GetConnectString();
            using (SqlConnection sqlConnection = new SqlConnection(strConnectString))
            {

                String insertString = "UPDATE Item SET ViewCount = @ViewCount WHERE ItemID = @ItemID";

                sqlConnection.Open(); // Open connection

                SqlCommand sqlCommand = new SqlCommand(); // Initialize an object SqlCommand
                sqlCommand.CommandType = CommandType.Text; // Specifies how a command string is interpreted.
                sqlCommand.CommandText = insertString; // Name of procudere want to execute
                sqlCommand.Connection = sqlConnection; // Set connection
                // Set parameter
                if (parameters != null) // If parameter is not null
                {
                    foreach (SqlParameter param in parameters) // Foreach parameters[]
                    {
                        sqlCommand.Parameters.Add(param); // Add param to Parameters
                    }
                }

                sqlCommand.ExecuteNonQuery();

                sqlCommand.Dispose(); // Dispose object SqlCommand com
                sqlConnection.Close(); // Close connection
            }

        }
        #endregion


        #region Delete...

        // <summary>
        /// Get table to Item
        /// </summary>
        /// <param name="procedureName">procedure name</param>
        /// <param name="parameters">parameter[]</param>
        /// <returns>void</returns>
        public void ExecuteDelete(List<SqlParameter> parameters)
        {
            string strConnectString = GetConnectString();
            using (SqlConnection sqlConnection = new SqlConnection(strConnectString))
            {

                String deleteString = "DELETE FROM Item WHERE ItemID = @ItemID";

                sqlConnection.Open(); // Open connection

                SqlCommand sqlCommand = new SqlCommand(); // Initialize an object SqlCommand
                sqlCommand.CommandType = CommandType.Text; // Specifies how a command string is interpreted.
                sqlCommand.CommandText = deleteString; // Name of procudere want to execute
                sqlCommand.Connection = sqlConnection; // Set connection
                // Set parameter
                if (parameters != null) // If parameter is not null
                {
                    foreach (SqlParameter param in parameters) // Foreach parameters[]
                    {
                        sqlCommand.Parameters.Add(param); // Add param to Parameters
                    }
                }

                sqlCommand.ExecuteNonQuery();

                sqlCommand.Dispose(); // Dispose object SqlCommand com
                sqlConnection.Close(); // Close connection
            }

        }

        #endregion
    
    }
}
